﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;


namespace PMS.DAL
{
    public class SemesterDAL
    {
        #region Common
        private static SemesterENT CreateSemesterFromReader(IDataReader reader)
        {
            SemesterENT item = new SemesterENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.SemesterID.ToString()))) item.SemesterID = Utility.IntParse(reader[SemesterColumns.SemesterID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.SemesterName.ToString()))) item.SemesterName = Utility.StringParse(reader[SemesterColumns.SemesterName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.FromDate.ToString()))) item.FromDate = Utility.DateTimeParse(reader[SemesterColumns.FromDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.ToDate.ToString()))) item.ToDate = Utility.DateTimeParse(reader[SemesterColumns.ToDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[SemesterColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[SemesterColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[SemesterColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[SemesterColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(SemesterColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[SemesterColumns.Isdeleted.ToString()]);

            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static SemesterENT GetSemesterByID(int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterGetByID.ToString());
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);

                SemesterENT semester = new SemesterENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        semester = CreateSemesterFromReader(reader);
                    }
                    reader.Close();
                }

                return semester;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSemesterByID");
            }
        }

        public static SemesterENT GetSemesterByDate(DateTime date)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterGetByDate.ToString());
                database.AddInParameter(dbCommand, "@Date", DbType.DateTime, date);

                SemesterENT semester = new SemesterENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        semester = CreateSemesterFromReader(reader);
                    }
                    reader.Close();
                }

                return semester;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSemesterByID");
            }
        }

        public static SemesterCollection GetSemesterList(string semestername, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterGetList.ToString());
                SemesterCollection listSemester = new SemesterCollection();
                database.AddInParameter(dbCommand, "@SemesterName", DbType.String, semestername);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.String, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSemester.Add(CreateSemesterFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listSemester;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSemesterList");
            }
        }

        public static SemesterCollection GetSemesterListForCMB()
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterGetListForCMB.ToString());

                SemesterCollection listSemester = new SemesterCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listSemester.Add(CreateSemesterFromReader(reader));
                    }
                    reader.Close();
                }

                return listSemester;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetSemesterListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertSemester(SemesterENT semester)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterInsert.ToString());
                int semesterID = 0;
                database.AddOutParameter(dbCommand, "@SemesterID", DbType.Int32, semester.SemesterID);
                database.AddInParameter(dbCommand, "@SemesterName", DbType.String, semester.SemesterName);
                database.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, semester.FromDate);
                database.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, semester.ToDate);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, semester.CreatedBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    semesterID = Utility.IntParse(database.GetParameterValue(dbCommand, "@SemesterID"));
                    return semesterID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertSemester");
            }
        }

        public static int UpdateSemester(SemesterENT semester)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterUpdate.ToString());

                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semester.SemesterID);
                database.AddInParameter(dbCommand, "@SemesterName", DbType.String, semester.SemesterName);
                database.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, semester.FromDate);
                database.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, semester.ToDate);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, semester.UpdateBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int semesterID = Utility.IntParse(database.GetParameterValue(dbCommand, "@SemesterID"));
                    return semesterID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateSemester");
            }
        }

        public static int DeleteSemester(int semesterID,int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procSemesterDelete.ToString());
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteSemester");
            }
        }
        #endregion
    }
}
